﻿Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.Data

Public Class ClsSqlData

    Dim _DBName As String
    Dim _UserId As String
    Dim _Password As String
    Dim _ServerName As String
    Dim _RefreshDatabases As Boolean
    Dim _RefreshTables As Boolean
    Private DbTypes As Dictionary(Of String, String)

    Sub New()
        DbTypes = New Dictionary(Of String, String)
    End Sub

    Public ReadOnly Property RefreshDatabases As Boolean
        Get
            Return _RefreshDatabases
        End Get
    End Property

    Public Property SqlServerName As String
        Get
            Return _ServerName
        End Get
        Set(ByVal value As String)
            If _ServerName <> value Then
                _RefreshDatabases = True
                _RefreshTables = True
            End If
            _ServerName = value
        End Set
    End Property
    Public Property DataBaseName As String
        Get
            Return _DBName
        End Get
        Set(ByVal value As String)
            Try
                If Me.GetDatabases.Contains(value) Then
                    If _DBName <> value Then
                        _RefreshDatabases = True
                        _RefreshTables = True
                    End If
                    _DBName = value
                Else
                    _DBName = ""
                End If
            Catch ex As Exception
                _DBName = ""
            End Try
        End Set
    End Property
    Public Property UserID As String
        Get
            Return _UserId
        End Get
        Set(ByVal value As String)
            If _UserId <> value Then
                _RefreshDatabases = True
                _RefreshTables = True
            End If
            _UserId = value
        End Set
    End Property
    Public Property password As String
        Get
            Return _Password
        End Get
        Set(ByVal value As String)
            If _Password <> value Then
                _RefreshDatabases = True
                _RefreshTables = True
            End If
            _Password = value
        End Set
    End Property

    Public Function ConnectionString(Optional Master As Boolean = False) As String
        If DataBaseName = "" Or Master Then
            Return "Data Source=" + _ServerName + ";Initial Catalog=Master;User ID=" + _UserId + ";Password=" + _Password
        Else

            Return "Data Source=" + _ServerName + ";Initial Catalog=" + DataBaseName + ";User ID=" + _UserId + ";Password=" + _Password
        End If
    End Function

    Public ReadOnly Property isValidConnection As Boolean
        Get
            Try
                Using cn As New SqlConnection(Me.ConnectionString)
                    cn.Open()
                    Return True
                End Using
            Catch ex As Exception
                Return False
            End Try
        End Get
    End Property


    Public Function GetInstances() As String()
        Dim tbl As DataTable
        Dim res() As String
        tbl = SqlDataSourceEnumerator.Instance.GetDataSources()
        res = (
                    From dr As DataRow In tbl.Rows Select CStr(
                                    IIf(
                                        dr("InstanceName") & "" = "",
                                        dr("ServerName").ToString,
                                        dr("ServerName").ToString + "\" + dr("InstanceName").ToString
                                        )
                                    )
                                ).ToArray
        Return res
    End Function
    Dim Databases() As String
    Public Function GetDatabases() As String()
        Try
            If Databases Is Nothing Or _RefreshDatabases Then
                Using tbl As New DataTable
                    Using sqladp As New SqlDataAdapter("SELECT NAME AS DBName FROM sys.databases d WHERE d.database_id>4", Me.ConnectionString(True))
                        sqladp.Fill(tbl)
                    End Using
                    Databases = (From dr As DataRow In tbl.Rows Select CStr(dr("DbName").ToString)).ToArray
                End Using
                Array.Sort(Databases)
                _RefreshDatabases = False
            End If
            Return Databases
        Catch
            Return Nothing
        End Try
    End Function
    Dim Tables() As String
    Public Function GetTables(Optional Refresh As Boolean = True) As String()
        Try
            If Tables Is Nothing Or _RefreshTables Or Refresh Then
                Using tbl As New DataTable
                    Using sqladp As New SqlDataAdapter("SELECT NAME AS TableName FROM sys.Tables d ORDER BY d.name ", Me.ConnectionString)
                        sqladp.Fill(tbl)
                    End Using
                    Tables = (From dr As DataRow In tbl.Rows Select CStr(dr("TableName").ToString)).ToArray
                End Using
                Array.Sort(Tables)
                _RefreshTables = False
            End If
            Return Tables
        Catch
            Return Nothing
        End Try
    End Function


    Public Function GetProcedures(ByVal TableName As String) As String()
        Dim res() As String
        Try
            Using tbl As New DataTable
                Dim sql As String
                If TableName = "" Then
                    sql = "SELECT NAME AS ProcName FROM sys.Procedures d "
                Else
                    sql = "SELECT o.name as ProcName, o.[type] " & vbCrLf _
                        & "FROM   sys.all_sql_modules s " & vbCrLf _
                        & "       INNER JOIN sys.sysobjects o " & vbCrLf _
                        & "            ON  o.id = s.[object_id] " & vbCrLf _
                        & "WHERE s.definition LIKE '%" + TableName + " %' AND  o.[type] = 'P'"
                End If

                Using sqladp As New SqlDataAdapter(sql, Me.ConnectionString)
                    sqladp.Fill(tbl)
                End Using
                res = (From dr As DataRow In tbl.Rows Select CStr(dr("ProcName").ToString)).ToArray
            End Using
            Return res
        Catch
            Return Nothing
        End Try
    End Function

    Public Function GetColumnNameArray(ByVal TableName As String) As String()
        Dim res() As String
        Try
            Using tbl As New DataTable
                Dim sql As String
                If TableName = "" Then
                    Return Nothing
                Else
                    sql = "SELECT C.name AS ColName FROM   sys.Columns C WHERE C.[object_id] = OBJECT_ID ('" + TableName + "')"
                End If

                Using sqladp As New SqlDataAdapter(sql, Me.ConnectionString)
                    sqladp.Fill(tbl)
                End Using
                res = (From dr As DataRow In tbl.Rows Select CStr(dr("ColName").ToString)).ToArray
            End Using
            Return res
        Catch
            Return Nothing
        End Try
    End Function

    Public Function GetColumnDetails(ByVal TableName As String) As DataTable
        Dim res As New DataTable
        Try
            Using tbl As New DataTable
                Dim sql As String
                If TableName = "" Then
                    Return Nothing
                Else
                    sql = "SELECT C.name AS ColName, TYPE_NAME(c.system_type_id) AS TypeName, c.max_length as MaxLength FROM   sys.Columns C WHERE C.[object_id] = OBJECT_ID ('" + TableName + "')"
                End If

                Using sqladp As New SqlDataAdapter(sql, Me.ConnectionString)
                    sqladp.Fill(tbl)
                End Using
                res = tbl.Copy
            End Using
            Return res
        Catch
            Return Nothing
        End Try
    End Function

    Public Sub FillColumnDetails(ByVal TableName As String, Table As DataTable)
        Dim res As New DataTable
        Try
            Dim sql As String
            If TableName <> "" Then
                sql = "SELECT C.name AS ColName, TYPE_NAME(c.system_type_id) AS TypeName, c.max_length as MaxLength FROM   sys.Columns C WHERE C.[object_id] = OBJECT_ID ('" + TableName + "')"
            Else
                Exit Sub
            End If
            Using sqladp As New SqlDataAdapter(sql, Me.ConnectionString)
                sqladp.Fill(Table)
            End Using
        Catch ex As Exception
            General.ShowErrorMessage(ex)
        End Try
    End Sub

    Public Function GetParameters(ByVal ProcName As String) As DataTable
        Dim res As DataTable
        Try
            Using tbl As New DataTable
                Using con As New SqlConnection(Me.ConnectionString)
                    con.Open()
                    Using cmd As New SqlCommand
                        cmd.Connection = con
                        cmd.CommandType = CommandType.Text
                        cmd.CommandText = "SELECT p.NAME AS ParamName,TYPE_NAME(p.system_type_id) as DbTypeName ,p.max_length as MaxLength FROM sys.parameters P WHERE p.[object_id] =OBJECT_ID(@objName)"
                        Dim param As New SqlParameter("@objName", SqlDbType.VarChar, 500)
                        cmd.Parameters.Add(param)
                        param.Value = ProcName
                        Using sqladp As New SqlDataAdapter(cmd)
                            sqladp.Fill(tbl)
                        End Using
                        res = tbl
                    End Using
                End Using
            End Using
            Return res
        Catch
            Return Nothing
        End Try
    End Function

    Public Sub FillParameters(ByVal ProcName As String, Table As DataTable)
        Dim res As DataTable
        Try

            Using con As New SqlConnection(Me.ConnectionString)
                con.Open()
                Using cmd As New SqlCommand
                    cmd.Connection = con
                    cmd.CommandType = CommandType.Text
                    cmd.CommandText = "SELECT p.NAME AS ParamName,TYPE_NAME(p.system_type_id) as DbTypeName ,p.max_length as MaxLength FROM sys.parameters P WHERE p.[object_id] =OBJECT_ID(@objName)"
                    Dim param As New SqlParameter("@objName", SqlDbType.VarChar, 500)
                    cmd.Parameters.Add(param)
                    param.Value = ProcName
                    Using sqladp As New SqlDataAdapter(cmd)
                        sqladp.Fill(Table)
                    End Using
                    res = Table
                End Using
            End Using

        Catch ex As Exception
            General.ShowErrorMessage(ex)
        End Try
    End Sub


    Public Function GetResult(Query As String) As DataTable
        Dim tbl As New DataTable
        Using adp As New SqlDataAdapter(Query, Me.ConnectionString)
            adp.Fill(tbl)
        End Using
        Return tbl
    End Function

    Public Sub FillResult(Query As String, Dataset As DataSet)
        Using adp As New SqlDataAdapter(Query, Me.ConnectionString)
            adp.Fill(Dataset)
        End Using
    End Sub



End Class
